--exec sp_products_list_with_odm_part_numbers_with_out_obsolete 0, 0, 1
alter	proc sp_products_list_with_odm_part_numbers_with_out_obsolete
@customer_id  int, @odm_id int, @is_sample int
as
begin
	declare @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (50), IST_Marketing_Part_Number varchar (50), Product_ID int, P_Type varchar (50), P_Comments varchar (200),Customer_PartNumber varchar(200),Label_Number varchar(50) )
	declare @proc_id varchar (20)
	declare @mft_part_no varchar (50)
	declare @mkt_part_no varchar (50)
	declare @prod_id int
	declare @p_type varchar (50)
	declare @p_description varchar (200)
	declare @customer_partnumber varchar(200)
	declare @label_number varchar(50)
	if @odm_id <= 0
	begin
		set @odm_id = null
	end
	
	insert into @results values(null,'---- CHIPS -----','---- CHIPS -----',null,null,null,null,null)
	
	if( @is_sample = 0 )
	begin
		insert into @results 
		select PS.ProcessStep_ID, P.Part_Number, P.P_Marketing_part_Number, P.Product_ID, P.P_Type, P.P_Comments, null, null from product P,Process_Step PS  where P.P_Type is null and 
					P.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and PART_STATUS <> 'Obsolete' and P.processStep_id=PS.ProcessStep_ID order by P.Part_Number
	end
	else
	begin
		insert into @results 
		select PS.ProcessStep_ID, P.Part_Number, P.P_Marketing_part_Number, P.Product_ID, P.P_Type, P.P_Comments,null, null from product P,Process_Step PS where P.P_Type is null and 
			P.Is_Approved = 0 and P.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and PART_STATUS <> 'Obsolete' and P.processStep_id=Ps.ProcessStep_ID order by P.Part_Number
	end
	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null,null,null)

	if( @is_sample = 0 )
	begin
		insert into @results
		select PS.ProcessStep_ID, P.Part_Number, P.P_Marketing_part_Number, P.Product_ID, P.P_Type, P.P_Comments, null, null from product P,Process_Step PS where P.P_Type = 'BOARD' and
				 P.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and P.processStep_id=P.ProcessStep_ID and P.P_Parent_Product_ID = -1 order by P.Part_Number
	end
	else
	begin
		insert into @results
		select PS.ProcessStep_ID, P.Part_Number, P.P_Marketing_part_Number, P.Product_ID, P.P_Type, P.P_Comments, null, null from product P,Process_Step PS  where P.P_Type = 'BOARD' 
			and P.Is_Approved = 0 and P.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and P.processStep_id=PS.ProcessStep_ID and P.P_Parent_Product_ID = -1 order by P.Part_Number
	end

	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null,null,null)

	if( @is_sample = 0 )
	begin
		insert into @results
		select PS.ProcessStep_ID, P.Part_Number, P.P_Marketing_part_Number, P.Product_ID, P.P_Type, P.P_Comments, null, null from product P,Process_Step PS where P.P_Type = 'KIT' and 
				P.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and P.processStep_id=PS.ProcessStep_ID order by P.Part_Number
	end
	else
	begin
		insert into @results
		select PS.ProcessStep_ID, P.Part_Number, P.P_Marketing_part_Number, P.Product_ID, P.P_Type, P.P_Comments, null, null from product P,Process_Step PS where P.P_Type = 'KIT' and
			 P.Is_Approved = 0 and P.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and P.processStep_id=PS.ProcessStep_ID order by P.Part_Number
	end
	
	select * from @results 
end